﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;

namespace NorthwindNewProductAddition
{
    class NorthwindNewProductAddition
    {
        const string ConnectionString = "Server=.; Database = Northwind; Integrated Security = true;";
        private static SqlConnection dbCon;

        private static void ConnectToDB()
        {
            dbCon = new SqlConnection(ConnectionString);
            dbCon.Open();
        }

        private static void DisconnectFromDB()
        {
            dbCon.Close();
        }

        private static void AddProduct(string name, int supplierId, int categoryId, string quantityPerUnit,
            decimal unitPrice, int unitsInStock, int unitsInOrder, int reorderLevel, int discontinued)
        {
            string commandText = "INSERT INTO Products " +
                "VALUES (@name, @supplierId, @categoryId, @quantityPerUnit, " +
                "@unitPrice, @unitsInStock, @unitsInOrder, @reorderLevel, @discontinued)";
            SqlCommand command = new SqlCommand(commandText, dbCon);
            command.Parameters.Add(new SqlParameter("@name", name));
            command.Parameters.Add(new SqlParameter("@supplierId", supplierId));
            command.Parameters.Add(new SqlParameter("@categoryId", categoryId));
            command.Parameters.Add(new SqlParameter("@quantityPerUnit", quantityPerUnit));
            command.Parameters.Add(new SqlParameter("@unitPrice", unitPrice));
            command.Parameters.Add(new SqlParameter("@unitsInStock", unitsInStock));
            command.Parameters.Add(new SqlParameter("@unitsInOrder", unitsInOrder));
            command.Parameters.Add(new SqlParameter("@reorderLevel", reorderLevel));
            command.Parameters.Add(new SqlParameter("@discontinued", discontinued));
            command.ExecuteNonQuery();
        }

        static void Main()
        {
            try
            {
                ConnectToDB();
                AddProduct("Liutenica", 7, 2, "1 jar", 3.2M, 10, 0, 0, 0);
            }
            finally
            {
                DisconnectFromDB();
            }
        }
    }
}
